<?php

namespace p5\objects;

use PDO;

class DBUtils
{

    /**
     * 执行一条 insert sql 操作,返回最新记录的ID (如果是自增长id的表)
     * @param $querys
     * @param string $db_id
     * @return int
     */
    public static function db_insert($querys, $db_id = "default")
    {
        return self::db_query($querys, $db_id, true);
    }

    public static function delete($sqlstr, $db_id = "default")
    {
        return self::do_query($sqlstr, $db_id, false);
    }

    public static function update($sqlstr, $db_id = "default")
    {
        return self::do_query($sqlstr, $db_id, false);
    }

    /**
     * 执行SQL操作,返回执行结果
     * @param $sqlstr
     * @param string $db_id
     * @param bool|false $return_last_id
     * @return int
     */
    public static function db_query($sqlstr, $db_id = "default", $return_last_id = false)
    {
        return self::do_query($sqlstr, $db_id, $return_last_id);
    }

    public static function do_query($sqlstr, $db_id = "default", $return_last_id = false)
    {
        return self::batch_query(array($sqlstr), $db_id, $return_last_id);
    }

    public static function batch_query($querys, $db_id = "default", $return_last_id = false)
    {
        global $dbs;
        if (count($querys) == 0) {
            return true;
        }
        if (!is_array($querys) && is_string($querys)) {
            $querys = array($querys);
        }
        if (empty($db_id)) {
            $db_id = "default";
        }
        if (self::pdo_support()) {
            return self::batch_query_in_pdo($querys, $db_id, $return_last_id);
        }
        $link = @mysql_connect($dbs[$db_id]["host"], $dbs[$db_id]["user"], $dbs[$db_id]["pass"]);
        mysql_query("SET NAMES '" . $dbs[$db_id]["mysql_encoding"] . "'");
        mysql_select_db($dbs[$db_id]["db_name"], $link);

        $result = 0;
        for ($i = 0; $i < count($querys); $i++) {
            mysql_query($querys[$i], $link);
            if ($return_last_id) {
                $result = mysql_insert_id();
            } else {
                $result = mysql_affected_rows();
                // print_html("affect rows : ".$result);
                $error = error_get_last();

                if ($error) {
                    LoggerUtils::error("======================================SQL 执行出错===========================================");
                    LoggerUtils::error($querys[$i]);
                    LoggerUtils::error($error["message"]);
                    LoggerUtils::error($querys[$i]);
                    LoggerUtils::error("==========================================================================================");
                    $result = -1;
                } else if ($result == 0) {
                    //
                } else if ($result == false) {
                    LoggerUtils::error("get false at: " . $querys[$i]);
                    $result = -1;
                }
            }
        }
        mysql_close($link);
        return $result;
    }

    public static function batch_query_in_pdo($querys, $db_id = "default", $return_last_id = false)
    {
        global $dbs;
        $dsn = "mysql:host=" . $dbs[$db_id]["host"] . ";dbname=" . $dbs[$db_id]["db_name"];
        if (isset($dbs[$db_id]["port"])) {
            $dsn .= ";port=" . $dbs[$db_id]["port"];
        }

        $db = new PDO($dsn, $dbs[$db_id]["user"], $dbs[$db_id]["pass"]);
        $db->exec("SET NAMES '" . $dbs[$db_id]["mysql_encoding"] . "'");
        $result = 0;
        for ($i = 0; $i < count($querys); $i++) {
            $count = $db->exec($querys[$i]);
            if ($count == false) {
                $result = -1;
            } else {
                if ($return_last_id) {
                    $result = $db->lastInsertId();
                }
            }
        }
        $db = null ;
        return $result;
    }


    /**
     * @param $table
     * @param $limit
     * @return int
     */
    public static function quick_delete($table, $limit)
    {
        $sqlstr = "delete from $table where $limit";
        return self::do_query($sqlstr);
    }

    //返回sql语句运行后影响的行数
    public static function get_affected_query($sqlstr, $db_id = "default")
    {
        global $dbs;
        $db_id = empty($db_id) ? "default" : $db_id;
        if (empty($sqlstr)) {
            return -1;
        }
        $link = @mysql_connect($dbs[$db_id]["host"], $dbs[$db_id]["user"], $dbs[$db_id]["pass"]);
        mysql_query("SET NAMES '" . $dbs[$db_id]["mysql_encoding"] . "'");
        mysql_select_db($dbs[$db_id]["db_name"], $link);
        mysql_query($sqlstr, $link);
        $result = mysql_affected_rows();
        mysql_close($link);
        return $result;
    }

    /**
     * 执行SQL语句,返回第一行的到一个数组对象中
     * @param $sqlstr
     * @param string $db_id
     * @return array
     */
    public static function query_first_row($sqlstr, $db_id = "default")
    {
        $result = @self::query_result($sqlstr, $db_id, MYSQL_ASSOC);
        if (count($result) > 0) {
            return $result[0];
        }
        return array();
    }

    /**
     * 执行SQL语句,将每行的第一列返回到一个数组对象
     * @param $sqlstr
     * @param string $db_id
     * @return array
     */
    public static function query_first_col($sqlstr, $db_id = "default")
    {
        $list = array();
        $matrix = self::query_result($sqlstr, $db_id, MYSQL_ASSOC);
        $key = "";
        if (count($matrix) > 0) {
            $keys = array_keys($matrix[0]);
            $key = $keys[0];
        }
        for ($i = 0; $i < count($matrix); $i++) {
            array_push($list, $matrix[$i][$key]);
        }
        return $list;
    }

    public static function query_into_hash($sqlstr, $db_id = "default")
    {
        $list = array();
        $matrix = self::query_result($sqlstr, $db_id, MYSQL_ASSOC);
        for ($i = 0; $i < count($matrix); $i++) {
            $labels = array_keys($matrix[$i]);
            $label0 = $labels[0];
            $label1 = $labels[1];
            $list[$matrix[$i][$label0]] = $matrix[$i][$label1];
        }
        return $list;
    }

    /**
     * 将结果集中每一行的记录两个属性取到一个key=value 的hash数组中
     * @param $matrix
     * @param string $keyColumnName
     * @param string $valueColumnName
     * @return array
     */
    public static function result_into_hash($matrix, $keyColumnName = "", $valueColumnName = "")
    {
        if (count($matrix) > 0) {
            if ($keyColumnName == "") {
                $labels = array_keys($matrix[0]);
                $keyColumnName = $labels[0];
                $valueColumnName = $labels[1];
            }
        }
        $list = array();
        for ($i = 0; $i < count($matrix); $i++) {
            $list[$matrix[$i][$keyColumnName]] = $matrix[$i][$valueColumnName];
        }
        return $list;
    }

    public static function query_into_map($sqlstr, $db_id = "default")
    {
        if (empty($sqlstr)) {
            LoggerUtils::warn("调用 query_into_map 方法时, sqlstr 参数不能为空");
            return array();
        }

        $matrix = self::query_result($sqlstr, $db_id, MYSQL_ASSOC);
        if ($matrix == false) {
            return array();
        }

        return self::result_to_map($matrix);
    }

    public static function result_to_map($matrix)
    {
        $list = array();
        if (is_array($matrix) && count($matrix) > 0) {
            for ($i = 0; $i < count($matrix); $i++) {
                $labels = array_values($matrix[$i]);
                $label = $labels[0];
                $list[$label] = $matrix[$i];
            }
        } else {
            if (!is_array($matrix)) {
                LoggerUtils::warn(json_encode($matrix) . " 不是有效的数组格式对象 ");
            }
        }
        return $list;
    }

    public static function query_result($sqlstr, $db_id = "default", $resultType = MYSQL_ASSOC)
    {
        global $dbs;
        $db_id = empty($db_id) ? "default" : $db_id;

        if (self::pdo_support()) {
            return self::query_result_in_pdo($sqlstr, $db_id, $resultType);
        }

        $matrix = array();
        $link = @mysql_connect($dbs[$db_id]["host"], $dbs[$db_id]["user"], $dbs[$db_id]["pass"], $dbs[$db_id]["port"]);
        mysql_query("SET NAMES '" . $dbs[$db_id]["mysql_encoding"] . "'");
        mysql_select_db($dbs[$db_id]["db_name"], $link);
        $result = mysql_query($sqlstr, $link);

        if ($result == false) {
            LoggerUtils::error("执行SQL操作出错,SQL:" . $sqlstr . ",结果:" . json_encode($result));
        } else if (mysql_num_rows($result) > 0) {
            while ($rows = mysql_fetch_array($result, $resultType)) {
                array_push($matrix, $rows);
            }
        }
        unset($result);
        mysql_close($link);
        return $matrix;
    }


    public static function query_result_in_pdo($sqlstr, $db_id = "default", $resultType = 'MYSQL_ASSOC')
    {
        global $dbs;
        $db_id = empty($db_id) ? "default" : $db_id;
        $matrix = array();
        $dsn = "mysql:host=" . $dbs[$db_id]["host"] . ";dbname=" . $dbs[$db_id]["db_name"];
        if (isset($dbs[$db_id]["port"])) {
            $dsn .= ";port=" . $dbs[$db_id]["port"];
        }
        // echo $dsn ;

        $db = new PDO($dsn, $dbs[$db_id]["user"], $dbs[$db_id]["pass"], array(PDO::ATTR_PERSISTENT => true));
        $db->exec("SET NAMES '" . $dbs[$db_id]["mysql_encoding"] . "'");
        $rs = $db->query($sqlstr);
        if ($rs == false) {
            LoggerUtils::error("执行SQL语句出错: " . $sqlstr);
        } else {
            $style = ($resultType == 'MYSQL_ASSOC') ? PDO::FETCH_ASSOC : PDO::FETCH_BOTH;
            while ($row = $rs->fetch($style)) {
                //	print_r($row);
                array_push($matrix, $row);
            }
        }
        unset($rs);
        $db = null ;
        return $matrix;
    }

    /**
     * 执行SQL语句,并对查询到的结果按照keyColumn的值分组,返回数据格式 {key1:[{},{},{}],key2:[{},{},{}]}
     * @param $sqlstr
     * @param $keyColumn
     * @param string $db_id
     * @return array
     */
    public static function query_with_group_by($sqlstr, $keyColumn, $db_id = "default")
    {
        $result = self::query_result($sqlstr, $db_id, MYSQL_ASSOC);
        $matrix = array();
        for ($i = 0; $i < count($result); $i++) {
            $temp = null;
            $key = $result[$i][$keyColumn];
            if (array_key_exists($key, $matrix)) {
                $temp = $matrix[$key];
            } else {
                $temp = array();
            }
            array_push($temp, $result[$i]);
            $matrix[$key] = $temp;
        }
        return $matrix;
    }

    /**
     * 执行SQL语句,获得第0行,第0列的值
     * @param $sql
     * @param string $db_id
     * @return string
     */
    public static function query_col00($sql, $db_id = "default")
    {
        $string = "";
        $result = @self::query_result($sql, $db_id, MYSQL_ASSOC);
        if (count($result) > 0) {
            $keys = array_keys($result[0]);
            $key = $keys[0];
            $string .= $result[0][$key];
        }
        unset($result);
        return $string;
    }

    /**
     * 从结果集中把每一行的某个属性取出来放到一个列表中. 如从用户结果集中分离出所有的用户id到一个数组
     * @param $result
     * @param string $column 列名(属性名),如 user_id
     * @param bool|false $distinct
     * @return array
     */
    public static function column_into_list($result, $column = "", $distinct = false)
    {
        if (empty($result)) {
            return array();
        }
        if (empty($column)) {
            $columns = array_keys($result[0]);
            $column = $columns[0];
        }
        $list = array();
        for ($i = 0; $i < count($result); $i++) {
            $value = $result[$i][$column];
            if ($distinct) {
                if (in_array($value, $list)) {
                    continue;
                }
            }
            array_push($list, $value);
        }
        return $list;
    }

    /**
     * 从结果集中查找一条 object[$key] = $value 的结果集, 如从用户列表中查询 mobile=1369999888 的用户
     * @param $result
     * @param $key
     * @param $value
     * @return array
     */
    public static function find_line($result, $key, $value)
    {
        for ($i = 0; $i < count($result); $i++) {
            $line = $result[$i];
            if ($value == $line[$key]) {
                return $line;
            }
        }
        return array();
    }

    public static function pdo_support()
    {
        $matrix = get_loaded_extensions();
        return in_array("pdo_mysql", $matrix);
    }

    public static function get_sql_safe_replaced($argName)
    {
        if (isset($_REQUEST[$argName])) {
            $value = self::sql_safe_replace($_REQUEST[$argName]);
            return $value;
        }
        return "";
    }

    public static function sql_safe_replace($value)
    {
        return str_replace("'", "''", $value);
    }

}
